package org.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import org.termine.entity.PersonalData;

@ManagedBean
@ApplicationScoped
public class PersonalDataDAO extends BaseDAO {
	
	public void addProvider(Connection connection, Integer personalDataId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into PROVIDER (PD_ID) values (?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalDataId);
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void addClient(Connection connection, Integer personalDataId) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into CLIENT (PD_ID) values (?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalDataId);
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void addPersonalData(Connection connection, PersonalData personalData) throws Exception {
		PreparedStatement statement = null;
		String statementString = "insert into PERSONAL_DATA (L_ID, PD_FORENAME, "
				+ "PD_SURNAME, PD_PHONE_NUMBER, PD_EMAIL, PD_PASSWORD) values (?,?,?,?,?,?)";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getLocation_id());
			statement.setString(2, personalData.getForename());
			statement.setString(3, personalData.getSurname());
			statement.setString(4, personalData.getPhone_number());
			statement.setString(5, personalData.getEmail());
			statement.setString(6, personalData.getPassword());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getProviderId(Connection connection, Integer personalDataId) throws Exception {
		Integer result = null;
		PreparedStatement statement = null;
		String statementString = "select P_ID from PROVIDER where PD_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalDataId);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getInt("P_ID");
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getClientId(Connection connection, Integer personalDataId) throws Exception {
		Integer result = null;
		PreparedStatement statement = null;
		String statementString = "select C_ID from CLIENT where PD_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalDataId);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getInt("C_ID");
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public Integer getPersonalDataId(Connection connection, PersonalData personalData) throws Exception {
		Integer result = null;
		PreparedStatement statement = null;
		String statementString = "select PD_ID from PERSONAL_DATA where L_ID = ? and "
				+ "PD_FORENAME = ? and PD_SURNAME = ? and PD_PHONE_NUMBER = ? and "
				+ "PD_EMAIL = ? and PD_PASSWORD = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getLocation_id());
			statement.setString(2, personalData.getForename());
			statement.setString(3, personalData.getSurname());
			statement.setString(4, personalData.getPhone_number());
			statement.setString(5, personalData.getEmail());
			statement.setString(6, personalData.getPassword());
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = new Integer(resultSet.getInt("PD_ID"));
				if (resultSet.wasNull()) {
					result = null;
				}
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public PersonalData getPersonalData(Connection connection, String email, String password) throws Exception {
		PreparedStatement statement = null;
		String statementString = "select * from PERSONAL_DATA where PD_EMAIL = ? and PD_PASSWORD = ?";
		try {
			statement = connection.prepareStatement(statementString);
			statement.setString(1, email);
			statement.setString(2, password);
			ResultSet resultSet = statement.executeQuery();

			if (resultSet.next()) {
				PersonalData result = new PersonalData();
				String string;
				Integer integer;

				integer = new Integer(resultSet.getInt("PD_ID"));
				if (!resultSet.wasNull()) {
					result.setPersonal_data_id(integer);
				}
				integer = new Integer(resultSet.getInt("L_ID"));
				if (!resultSet.wasNull()) {
					result.setLocation_id(integer);
				}
				integer = new Integer(resultSet.getInt("C_ID"));
				if (!resultSet.wasNull()) {
					result.setClient_id(integer);
				}
				integer = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					result.setProvider_id(integer);
				}
				string = resultSet.getString("PD_FORENAME");
				if (!resultSet.wasNull()) {
					result.setForename(string);
				}
				string = resultSet.getString("PD_SURNAME");
				if (!resultSet.wasNull()) {
					result.setSurname(string);
				}
				string = resultSet.getString("PD_PHONE_NUMBER");
				if (!resultSet.wasNull()) {
					result.setPhone_number(string);
				}
				string = resultSet.getString("PD_EMAIL");
				if (!resultSet.wasNull()) {
					result.setEmail(string);
				}
				string = resultSet.getString("PD_PASSWORD");
				if (!resultSet.wasNull()) {
					result.setPassword(string);
				}
				return result;
			} else {
				return null;
			}	
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}

	public void updatePersonalData(Connection connection, PersonalData personalData) throws Exception {
		PreparedStatement statement = null;
		String statementString = "update PERSONAL_DATA set L_ID = ?, C_ID = ?, "
				+ "P_ID = ?, PD_FORENAME = ?, PD_SURNAME = ?, PD_PHONE_NUMBER = ?, " 
				+ "PD_EMAIL = ?, PD_PASSWORD = ? where PD_ID = ?";
		try {
			statement = connection.prepareStatement(statementString);
			if (personalData.getLocation_id() != null) {
				statement.setInt(1, personalData.getLocation_id());
			} else {
				statement.setNull(1, java.sql.Types.INTEGER);
			}
			if (personalData.getClient_id() != null) {
				statement.setInt(2, personalData.getClient_id());
			} else {
				statement.setNull(2, java.sql.Types.INTEGER);
			}
			if (personalData.getProvider_id() != null) {
				statement.setInt(3, personalData.getProvider_id());
			} else {
				statement.setNull(3, java.sql.Types.INTEGER);
			}
			statement.setString(4, personalData.getForename());
			statement.setString(5, personalData.getSurname());
			statement.setString(6, personalData.getPhone_number());
			statement.setString(7, personalData.getEmail());
			statement.setString(8, personalData.getPassword());
			statement.setInt(9, personalData.getPersonal_data_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
		}
	}
}
